# _*_ coding:utf-8 _*_
# @File  : ruizy_user.py
# @Time  : 2022-01-10  09:37
# @Author: zizle


# 新增用户和用户登录等
import datetime
import json
import pathlib
import re
import time

import pandas as pd
from fastapi import APIRouter, Body, Header, Query
from pydantic import BaseModel, constr

from db import FAConnection, utils as dbutils
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import char_utils, datetime_utils
from v1_all_api import constant
from hutool import security
from configs import FILE_STORAGE, STATIC_FILE_HOST

user_api = APIRouter()


class UserItem(BaseModel):
    id: int = -1
    nickname: str
    phone: str = ''
    admin_name: str
    password: str = ''
    expire: str
    role_code: int = 1000
    out_role: int = 0
    vip_expire: str = '2020/12/31'
    is_analyst: int = 0
    ruizy_code: str = ''


@user_api.post('/user/')  # 用户注册或修改
def create_user(user_item: UserItem = Body(...)):
    expire_date = datetime_utils.auth_datetime_string(user_item.expire, f='%Y/%m/%d')
    vip_expire_date = datetime_utils.auth_datetime_string(user_item.vip_expire, f='%Y/%m/%d')
    if not expire_date or not vip_expire_date:
        return AllResponse.validate_error(msg='日期格式无法格式化为%Y/%m/%d')
    if user_item.id < 1:
        ruizy_code = user_item.ruizy_code
        if not ruizy_code:
            ruizy_code = char_utils.generate_random_string(ismix=False, length=10)
        nickname = user_item.nickname if user_item.nickname else user_item.phone
        if not nickname:
            nickname = ruizy_code
        if user_item.phone and re.match(r'^1[3456789]\d{9}$', user_item.phone):
            password_raw = user_item.password if user_item.password else user_item.phone[-6:]
        else:
            password_raw = '123456'
            if user_item.password:
                password_raw = user_item.password
        password = security.cipher_user_password(password_raw)
        if user_item.role_code == 2000:  # 营业部用户
            ruizy_code = nickname
            nickname = user_item.admin_name
        op_sql = 'INSERT INTO ruizy_user (ruizy_code,nickname,phone,password,admin_name,role_code,out_role,expire,vip_expire) ' \
                 'VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);'
        op_par = [ruizy_code, nickname, user_item.phone, password, user_item.admin_name, user_item.role_code,
                  user_item.out_role, expire_date, vip_expire_date]
    else:
        # 更新用户信息
        if user_item.password:
            password = security.cipher_user_password(user_item.password)
            op_sql = """
                UPDATE ruizy_user SET nickname=%s,phone=%s,expire=%s,vip_expire=%s,role_code=%s,
                out_role=%s,admin_name=%s,phone=%s,
                password=%s,is_analyst=%s WHERE id=%s;
            """
            op_par = [user_item.nickname, user_item.phone, expire_date, vip_expire_date, user_item.role_code,
                      user_item.out_role, user_item.admin_name, user_item.phone, password, user_item.is_analyst, user_item.id]
        else:
            op_sql = """
                UPDATE ruizy_user SET nickname=%s,phone=%s,expire=%s,vip_expire=%s,role_code=%s,out_role=%s,
                admin_name=%s,phone=%s,
                is_analyst=%s WHERE id=%s;
            """
            op_par = [user_item.nickname, user_item.phone, expire_date, vip_expire_date, user_item.role_code, user_item.out_role,
                      user_item.admin_name, user_item.phone, user_item.is_analyst, user_item.id]
    db_conn = FAConnection()
    _, success = db_conn.execute(op_sql, param=op_par)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class LoginItem(BaseModel):
    account: str
    password: str


def db_user_login(account, password):
    db_conn = FAConnection()
    query_sql = """
        SELECT id,password,role_code,out_role,expire,vip_expire,is_analyst 
        FROM ruizy_user WHERE ruizy_code=%s OR phone=%s LIMIT 1;
    """
    user_obj = db_conn.query(query_sql, param=[account, account], fetchone=True)[0]
    if not user_obj:
        return AllResponse.server_no_content(msg='用户不存在,登录失败!')
    if not security.decipher_user_password(password, user_obj['password']):
        return AllResponse.validate_error(msg='用户名或密码错误!')
    # 检查有效期
    if user_obj['expire'].strftime('%Y-%m-%d') < datetime.datetime.today().strftime('%Y-%m-%d'):
        return AllResponse.validate_error(msg='账户已过有效期，登录失败！')
    payload = {
        'uid': user_obj['id'],
        'role_code': user_obj['role_code'],
        'out_role': user_obj['out_role']
    }
    token = security.create_access_token(payload)
    return AllResponse.operate_successfully(data={'Ruizy-Token': token}, msg='登录成功!')


@user_api.post('/login/')  # 用户登录
def login_user(login_item: LoginItem = Body(...)):
    return db_user_login(login_item.account, login_item.password)


@user_api.post('/Login/')  # 用户RSA登录
def login_by_rsa(param: str = Body(..., embed=True)):
    login_data = eval(security.rsa_decrypt(param, key_tuple=0))
    if not login_data:
        return AllResponse.validate_error()
    return db_user_login(login_data["username"], login_data["password"])


@user_api.post('/userInfo/')  # 用户登录后获取信息
def information_user(ruizy_token: str = Header(...)):
    token_payload = security.decrypt_access_token(ruizy_token)
    if not token_payload:
        return AllResponse.no_authorization()

    # if token_payload['exp'] < 1657893600:
    #     return AllResponse.no_authorization()

    # 查询数据
    db_conn = FAConnection()
    query_sql = 'SELECT id,create_time,ruizy_code,nickname,phone,password,expire,admin_name,vip_expire ' \
                'FROM ruizy_user WHERE id=%s;'
    user_obj = db_conn.query(query_sql, param=[token_payload['uid']], fetchone=True)[0]
    if not user_obj:
        return AllResponse.no_authorization()
    response_data = {
        'avatar': 'https://i.gtimg.cn/club/item/face/img/2/15922_100.gif',
        'username': user_obj['nickname'],
        'permissions': ['free']  # 按前端写法，必须给一个，否则前端会一直请求菜单(这个permissions需配合菜单权限)，目前无用
    }
    return AllResponse.operate_successfully(response_data)


@user_api.post('/refreshToken/')  # 刷新token
def refresh_token(ruizy_token: str = Header(...)):
    token_payload = security.decrypt_access_token(ruizy_token)
    if not token_payload:
        return AllResponse.no_authorization()
    # 查看token剩余时间,不足7小时的，重新发放
    # print(token_payload)
    # print(token_payload['exp'] - int(time.time()), 7 * 60 *60)
    if token_payload['exp'] - int(time.time()) < 7 * 60 * 60:
        new_token = security.create_access_token({
            'uid': token_payload['uid'],
            'role_code': token_payload['role_code'],
            'out_role': token_payload['out_role']
        }, expire_seconds=int(7.5 * 60 * 60))  # 刷新的只发7.5小时
    else:
        new_token = ruizy_token
    return AllResponse.operate_successfully(data={'Ruizy-Token': new_token}, msg='刷新token成功!')


@user_api.get('/userInfo/')  # 用户获取个人中心资料
def user_center_information(ruizy_token: str = Header(...), uid: int = Query(None)):
    """携带uid查询参数，并且操作者是管理员时，可查询对应uid的资料"""
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if uid:
        # 查询的是别的用户
        person['uid'] = uid
        if not security.operator_is_manager(person):  # 不是管理员
            return AllResponse.forbidden_error()
    # 基础资料
    sql = """
        SELECT u.id,u.create_time,u.ruizy_code,u.nickname,u.phone,u.role_code,u.out_role,u.expire,u.vip_expire,u.admin_name,
          u.is_analyst   
        FROM ruizy_user As u WHERE u.id=%s;
    """
    sql_info = """
        SELECT id,join_date,phone,school,qualification,office_location,department,group_name,position_name,
        certificate1_date,certificate2_date,
        work_area,certificate,prizes
        FROM ruizy_user_info
        WHERE user_id=%s;
    """
    with FAConnection() as cursor:
        cursor.execute(sql, [person['uid']])
        user_obj = cursor.fetchone()
        # 查询拓展信息
        cursor.execute(sql_info, [person['uid']])
        user_info = cursor.fetchone()
    if not user_obj:
        return AllResponse.server_no_content(msg='用户不存在')
    join_duration = '未知'
    if user_info and user_info.get('join_date'):
        # 计算入职年限
        join_duration = datetime_utils.date_interval(user_info['join_date'], datetime.date.today(), to_string=True)
    show_certificates = False
    show_prizes = False
    if user_obj['out_role'] in [constant.RESEARCHER, constant.DEVELOPER, constant.INVESTMENT_MANAGER,
                                constant.INVESTMENT_CONSULTING, constant.ASSET_SUPPORTER]:
        base_info = [
            {'key': 'phone', 'title': '电话', 'text': '未知', 'canEdit': True},
            {'key': 'join_date', 'title': '入职日期', 'text': '未知', 'canEdit': True},
            {'key': 'join_duration', 'title': '入职年限', 'text': join_duration},
            {'key': 'school', 'title': '毕业院校', 'text': '未知', 'canEdit': True},
            {'key': 'qualification', 'title': '学历学位', 'text': '未知', 'canEdit': True},
            {'key': 'office_location', 'title': '办公地点', 'text': '未知', 'canEdit': True},
            {'key': 'department', 'title': '部门', 'text': '未知', 'canEdit': True},
            {'key': 'group_name', 'title': '小组', 'text': '未知', 'canEdit': True},
            {'key': 'position_name', 'title': '职位', 'text': '未知', 'canEdit': True},
            {'key': 'work_area', 'title': '负责领域', 'text': '未知', 'canEdit': True},
            {'key': 'certificate1_date', 'title': '从业证通过日期', 'text': '未知', 'canEdit': True},
            {'key': 'certificate2_date', 'title': '咨询证通过日期', 'text': '未知', 'canEdit': True},

        ]
        show_certificates = True
        show_prizes = True
    elif user_obj['out_role'] in [constant.SALES_MAN]:
        base_info = [
            {'key': 'phone', 'title': '电话', 'text': '未知', 'canEdit': True},
            {'key': 'department', 'title': '营业部', 'text': '未知', 'canEdit': True},
            {'key': 'position_name', 'title': '职务', 'text': '未知', 'canEdit': True},
        ]
    elif user_obj['out_role'] in [constant.CUSTOMER]:
        base_info = [
            {'key': 'phone', 'title': '电话', 'text': '未知', 'canEdit': True},
            {'key': 'department', 'title': '单位', 'text': '未知', 'canEdit': True},
            {'key': 'position_name', 'title': '职务', 'text': '未知', 'canEdit': True},
            {'key': 'introducer', 'title': '推荐人', 'text': '无', 'canEdit': True},
        ]
    else:
        base_info = [
            {'key': 'phone', 'title': '电话', 'text': '未知', 'canEdit': True},
        ]
    certificates = []
    prizes = []
    if user_info:
        for k, v in user_info.items():
            for b_row in base_info:
                if b_row['key'] == k:
                    b_row['text'] = v
                    break
        certificates = list(sorted(json.loads(user_info['certificate']), key=lambda x: x['date'], reverse=True))
        prizes = list(sorted(json.loads(user_info['prizes']), key=lambda x: x['date'], reverse=True))
    role_code_text = constant.ROLE_CODE_NAMES.get(user_obj['role_code'], '普通用户')
    out_role_text = constant.OUT_ROLE_NAMES.get(user_obj['out_role'], '其他')
    resp_info = {
        'sys_info': [
            {'title': '瑞智号', 'text': user_obj.get('ruizy_code', '未知'), 'key': 'ruizy_code', 'edit': False},
            {'title': '加入日期', 'text': user_obj['create_time'].strftime('%Y-%m-%d'), 'key': 'create_time', 'edit': False},
            {'title': '有效期', 'text': user_obj['expire'], 'key': 'expire', 'edit': True},
            {'title': '昵称', 'text': user_obj['nickname'], 'key': 'nickname', 'edit': True},
            {'title': '品种角色', 'text': role_code_text, 'key': 'role_code', 'edit': True, 'role_code': user_obj['role_code']},
            {'title': '身份角色', 'text': out_role_text, 'key': 'out_role', 'edit': True, 'out_role': user_obj['out_role']},
            {'title': '登录手机', 'text': user_obj['phone'], 'key': 'phone', 'edit': True},
            {'title': '备注名', 'text': user_obj['admin_name'], 'key': 'admin_name', 'edit': True},
            {'title': '分析师', 'text': user_obj['is_analyst'], 'key': 'is_analyst', 'edit': True, 'is_analyst': user_obj['is_analyst']},
            {'title': '密码', 'text': '', 'key': 'password', 'edit': True},
        ],
        'base_info': base_info,
        'certificates': certificates,
        'prizes': prizes,
        'show_certificates': show_certificates,
        'show_prizes': show_prizes,
    }
    return AllResponse.operate_successfully(data=resp_info)


@user_api.put('/userInfo/')  # 用户修改个人中心资料
def user_center_information_modify(ruizy_token: str = Header(...), body: dict = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 可直接更新的数据
    direct_dict = {k: v for k, v in body.items() if k in ['phone', 'join_date', 'certificate1_date', 'certificate2_date',
                                                          'school', 'qualification', 'office_location', 'department', 'group_name', 'position_name', 'work_area']}
    # 证书和奖项不会在这里修改,要么直接删除，要么新增
    # certificate = body.get('certificate')
    # prizes = body.get('prizes')
    # 需要验证格式的数据
    join_date = datetime_utils.auth_datetime_string(str(body.get('join_date')), '%Y-%m-%d')
    certificate1_date = datetime_utils.auth_datetime_string(str(body.get('certificate1_date')), '%Y-%m-%d')
    certificate2_date = datetime_utils.auth_datetime_string(str(body.get('certificate2_date')), '%Y-%m-%d')
    if body.get('join_date') and not join_date:
        return AllResponse.validate_error(msg='入职日期格式错误,接受YYYY-MM-DD类型')
    if body.get('certificate1_date') and not certificate1_date:
        return AllResponse.validate_error(msg='从业证通过日期格式错误,接受YYYY-MM-DD类型')
    if body.get('certificate2_date') and not certificate2_date:
        return AllResponse.validate_error(msg='咨询证通过日期格式错误,接受YYYY-MM-DD类型')
    if not direct_dict:
        return AllResponse.validate_error(msg='没有检测到需要修改的数据.')
    for dk in ['certificate1_date', 'certificate2_date']:
        if not direct_dict.get(dk):
            direct_dict[dk] = None
    # 查询有没此用户的数据，有更新无则新增
    exist_sql = 'SELECT id FROM ruizy_user_info WHERE user_id=%s LIMIT 1;'
    u_sql = 'UPDATE ruizy_user_info SET ' + dbutils.create_sql(direct_dict) + ' WHERE user_id=%(user_id)s LIMIT 1;'
    direct_dict['user_id'] = person['uid']
    with FAConnection() as cursor:
        cursor.execute(exist_sql, [person['uid']])
        exist = cursor.fetchone()
        if exist:
            cursor.execute(u_sql, direct_dict)
        else:
            direct_dict['certificate'] = "[]"
            direct_dict['prizes'] = "[]"
            cols = ','.join(direct_dict.keys())
            values = ','.join(['%%(%s)s' % key for key in direct_dict.keys()])
            insert_sql = 'INSERT INTO ruizy_user_info (%s) VALUES (%s);' % (cols, values)
            cursor.execute(insert_sql, direct_dict)
    return AllResponse.operate_successfully()


@user_api.post('/userInfo/certificatePrize/')  # 新增证书奖项
def modify_user_certificate_prize(ruizy_token: str = Header(...), body: dict = Body(...)):
    """
    certificate: {'date': '', 'name': '', 'keyid': timestamp+_06d uid} # ''.join(str(uuid.uuid4()).split('-'))
    prizes: {'date': '', 'name': '', 'keyid': timestamp+_06d uid}
    """
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    r_key = body.get('key_name')
    if not r_key or r_key not in ['certificate', 'prizes']:
        return AllResponse.validate_error(msg='添加的数据类型错误')

    r_date = datetime_utils.auth_datetime_string(str(body.get('date')), f='%Y-%m-%d')
    if not r_date:
        return AllResponse.validate_error(msg='日期格式错误,接受YYYY-MM-DD类型')

    exist_sql = 'SELECT id,certificate,prizes FROM ruizy_user_info WHERE user_id=%s LIMIT 1;'
    insert_sql = 'INSERT INTO ruizy_user_info (certificate,prizes,user_id) VALUES (%s,%s,%s);'
    update_sql = 'UPDATE ruizy_user_info SET certificate=%s,prizes=%s WHERE user_id=%s LIMIT 1;'
    certificate = []
    prizes = []
    with FAConnection() as cursor:
        cursor.execute(exist_sql, [person['uid']])
        exist_obj = cursor.fetchone()
        if exist_obj:
            certificate = json.loads(exist_obj['certificate'])
            prizes = json.loads(exist_obj['prizes'])
            exe_sql = update_sql
        else:
            exe_sql = insert_sql
        if r_key == 'certificate':  # 证书
            certificate.append({
                'date': r_date,
                'name': body.get('name'),
                'code': body.get('code'),
                'keyid': str(int(time.time() * 1000)) + '_%06d' % person['uid']
            })
        else:
            prizes.append({
                'date': r_date,
                'name': body.get('name'),
                'code': '',  # 奖项没有编号
                'keyid': str(int(time.time() * 1000)) + '_%06d' % person['uid']
            })
        exe_prm = [json.dumps(certificate, ensure_ascii=False), json.dumps(prizes, ensure_ascii=False), person['uid']]
        cursor.execute(exe_sql, exe_prm)
    return AllResponse.operate_successfully()


@user_api.delete('/userInfo/certificatePrize/')  # 删除证书选项的记录
def delete_user_certificate_prize(ruizy_token: str = Header(...), body: dict = Body(...)):
    # body： {key_name: '', keyid: ''}
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    r_key = body.get('key_name')
    if not r_key or r_key not in ['certificate', 'prizes']:
        return AllResponse.validate_error(msg='操作的数据类型错误')
    exist_sql = 'SELECT id,certificate,prizes FROM ruizy_user_info WHERE user_id=%s LIMIT 1;'
    with FAConnection() as cursor:
        cursor.execute(exist_sql, [person['uid']])
        exist_obj = cursor.fetchone()
        certificates = json.loads(exist_obj['certificate'])
        prizes = json.loads(exist_obj['prizes'])
        del_keyid = body.get('keyid', '')

        if r_key == 'certificate':
            certificates = []
            for row in json.loads(exist_obj['certificate']):
                if row['keyid'] == del_keyid:
                    continue
                else:
                    certificates.append(row)
        elif r_key == 'prizes':
            prizes = []
            for row in json.loads(exist_obj['prizes']):
                if row['keyid'] == del_keyid:
                    continue
                else:
                    prizes.append(row)
        else:
            pass

        cursor.execute("UPDATE ruizy_user_info SET certificate=%s,prizes=%s WHERE id=%s LIMIT 1;",
                       [json.dumps(certificates, ensure_ascii=False), json.dumps(prizes, ensure_ascii=False),
                        exist_obj['id']])
    return AllResponse.operate_successfully(msg='删除成功!')


@user_api.get('/userListInfo/')  # 查询用户列表的资料
def query_user_list_information(ruizy_token: str = Header(...), role: int = Query(...), active: str = Query('0'),
                                export_flag: bool = Query(False)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    # 查询相关的人员资料列表
    sql = """
        SELECT u.id,u.admin_name,u.ruizy_code,u.nickname,u.phone as login_phone,u.create_time,u.expire,u.role_code,
          i.id as info_id,i.join_date,i.certificate1_date,i.certificate2_date,i.phone,i.school,i.qualification,i.office_location,
          i.department,i.group_name,i.position_name,i.introducer,i.work_area,i.certificate,i.prizes
        FROM ruizy_user As u 
        LEFT JOIN ruizy_user_info As i ON u.id=i.user_id
        WHERE u.out_role=%s
    """
    prm = [role]
    if active == '0':
        sql += ';'
    elif active == '1':
        sql += ' AND expire>=%s;'
        prm.append(datetime_utils.today(f='%Y-%m-%d'))
    elif active == '-1':
        sql += ' AND expire<=%s;'
        prm.append(datetime_utils.today(f='%Y-%m-%d'))
    else:
        return AllResponse.validate_error(msg='参数错误')
    with FAConnection() as cursor:
        cursor.execute(sql, prm)
        user_list = cursor.fetchall()
    for u in user_list:
        if u['info_id']:
            if u['join_date']:
                # 计算入职年限
                u['join_duration'] = datetime_utils.date_interval(u['join_date'], datetime.date.today(), to_string=True)
            u['certificate'] = list(sorted(json.loads(u['certificate']), key=lambda x: x['date'], reverse=True))
            u['prizes'] = list(sorted(json.loads(u['prizes']), key=lambda x: x['date'], reverse=True))
            prizes_text = ''
            certificates_text = ''
            for cft in u['certificate']:
                certificates_text = certificates_text + cft['date'][0:7] + '  '
                if cft['code']:
                    certificates_text = certificates_text + f'({cft["code"]})'
                certificates_text = certificates_text + cft['name'] + '\n'
            for pze in u['prizes']:
                prizes_text = prizes_text + pze['date'][0:7] + '  '
                prizes_text = prizes_text + pze['name'] + '\n'
            u['certificates_text'] = certificates_text
            u['prizes_text'] = prizes_text
        # ------------ 管理功能需要的信息----------
        # 管理功能，查看是否需要管理品种
        u['auth_variety'] = True
        if u['role_code'] in [constant.ADMIN_ROLE_CODE, constant.MANAGE_ROLE_CODE, constant.SPECIAL_ANALYST]:
            u['auth_variety'] = False
        u['create_time'] = u['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        # ------------ 管理功能需要的信息----------
    if export_flag:
        # 导出为excel
        df = pd.DataFrame(user_list)
        df['index'] = df.index + 1
        now_ts = datetime_utils.today_timestamp(millisecond=True)
        tmp_filename = pathlib.Path(FILE_STORAGE).joinpath('Temp/用户信息表{}.xlsx'.format(now_ts))
        if role in [constant.RESEARCHER, constant.DEVELOPER, constant.INVESTMENT_MANAGER,
                    constant.INVESTMENT_CONSULTING, constant.ASSET_SUPPORTER]:
            base_info = [
                {'key': 'phone', 'title': '电话'},
                {'key': 'join_date', 'title': '入职日期'},
                {'key': 'join_duration', 'title': '入职年限'},
                {'key': 'school', 'title': '毕业院校'},
                {'key': 'qualification', 'title': '学历学位'},
                {'key': 'office_location', 'title': '办公地点'},
                {'key': 'department', 'title': '部门'},
                {'key': 'group_name', 'title': '小组'},
                {'key': 'position_name', 'title': '职位'},
                {'key': 'work_area', 'title': '负责领域'},
                {'key': 'certificates_text', 'title': '资格证书'},
                {'key': 'prizes_text', 'title': '获奖情况'},
            ]
        elif role in [constant.SALES_MAN]:
            base_info = [
                {'key': 'phone', 'title': '电话'},
                {'key': 'department', 'title': '营业部'},
                {'key': 'position_name', 'title': '职务'},
            ]
        elif role in [constant.CUSTOMER]:
            base_info = [
                {'key': 'phone', 'title': '电话'},
                {'key': 'department', 'title': '单位'},
                {'key': 'position_name', 'title': '职务'},
                {'key': 'introducer', 'title': '推荐人'},
            ]
        else:
            base_info = [
                {'key': 'phone', 'title': '电话'},
            ]
        base_info.insert(0, {'key': 'admin_name', 'title': '姓名'})
        base_info.insert(0, {'key': 'index', 'title': '序号'})
        df = df[[item['key'] for item in base_info]]
        df.rename(columns={item['key']: item['title'] for item in base_info}, inplace=True)
        df.to_excel(tmp_filename, encoding='utf8', index=False)
        return AllResponse.operate_successfully(data={'file_url': str(tmp_filename).replace('\\', '/').replace(FILE_STORAGE, STATIC_FILE_HOST)})
    return AllResponse.operate_successfully(data=user_list)


class PasswordItem(BaseModel):
    raw_password: constr(min_length=6)
    new_password1: constr(min_length=6, max_length=20)
    new_password2: constr(min_length=6, max_length=20)


@user_api.post('/password/')  # 用户修改密码
def modify_password(ruizy_token: str = Header(...), password_item: PasswordItem = Body(...)):
    user_payload = security.decrypt_access_token(ruizy_token)
    if not user_payload:
        return AllResponse.no_authorization()
    if password_item.new_password1 != password_item.new_password2:
        return AllResponse.validate_error(msg='两次输入密码不一致!')
    db_conn = FAConnection()
    user_obj = db_conn.query('SELECT id,password FROM ruizy_user WHERE id=%s;', param=[user_payload['uid']], keep_conn=True, fetchone=True)[0]
    if not user_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='用户不存在!')
    # 对比旧密码
    if not security.decipher_user_password(password_item.raw_password, user_obj['password']):
        db_conn.close()
        return AllResponse.validate_error(msg='原密码输入错误,修改失败!')
    # 对比新密码与旧密码
    if security.cipher_user_password(password_item.new_password1) == user_obj['password']:
        db_conn.close()
        return AllResponse.validate_error(msg='新密码与原密码一样!')
    # 更新新密码
    update_sql = 'UPDATE ruizy_user SET password=%s WHERE id=%s LIMIT 1;'
    update_prm = [security.cipher_user_password(password_item.new_password1), user_obj['id']]
    _, success = db_conn.execute(update_sql, param=update_prm)
    if success:
        return AllResponse.operate_successfully(msg='修改成功!')
    else:
        return AllResponse.server_error()


@user_api.get('/userListByName/')  # 通过名称查询用户
def get_user_by_name(username: str = Query(...)):
    query_sql = 'SELECT id,admin_name,role_code,out_role FROM ruizy_user WHERE nickname LIKE %s OR admin_name LIKE %s;'
    db_conn = FAConnection()
    params = [f'%{username}%', f'%{username}%']
    records = db_conn.query(query_sql, params)
    for u in records:
        u['sys_role_name'] = constant.ROLE_CODE_NAMES.get(u['role_code'], '未知')
        u['out_role_name'] = constant.OUT_ROLE_NAMES.get(u['out_role'], '未知')
    return AllResponse.operate_successfully(records, msg='查询成功!')


@user_api.get('/userList/')  # 管理员获取用户列表（待废弃）等按角色维护功能开发出来即可废弃
def get_user_list(ruizy_token: str = Header(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_super_admin(person):
        return AllResponse.forbidden_error()
    query_sql = """
        SELECT u.id,u.create_time,u.ruizy_code,u.nickname,u.phone,u.expire,u.admin_name,u.role_code,u.out_role,
        b.role_name  
        FROM ruizy_user As u 
        INNER JOIN ruizy_role As b ON u.role_code=b.role_code;
    """
    db_conn = FAConnection()
    records = db_conn.query(query_sql)
    today = datetime_utils.today()
    for u in records:
        u['create_time'] = u['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        u['active'] = u['expire'].strftime('%Y%m%d') >= today  # 有效
        u['out_role_name'] = constant.OUT_ROLE_NAMES.get(u['out_role'], '未知')
    records.sort(key=lambda x: x['active'], reverse=True)
    return AllResponse.operate_successfully(data=records)


@user_api.get('/user/analyst/')  # 获取分析师列表（）也可废弃，但注意用到的地方
def get_analyst():
    query_sql = 'SELECT id,nickname,admin_name,expire FROM ruizy_user WHERE is_analyst=1;'
    db_conn = FAConnection()
    records = db_conn.query(query_sql)
    # 过滤掉已经失效60天的
    today = datetime_utils.today()
    records = list(filter(lambda x: int(x['expire'].strftime('%Y%m%d')) - int(today) > -60, records))
    return AllResponse.operate_successfully(data=records)
